package jxl;

import java.io.File;
import java.lang.reflect.Field;
import java.util.List;

import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.WritableFont.FontName;
import jxl.write.biff.RowsExceededException;

public class JxlUtil {

	/** 字段列表分隔符 **/
	public static final String FIELD_SEPARATOR=",";
	/** 列宽为0 **/
	public static final int NO_COLUMN_VIEW=0;
	
	/**
	 * 增加表格的头部
	 * @param sheet
	 * @param startColumn		开始列
	 * @param row				行号
	 * @param headerList		头字段List
	 * @param wcfFormat
	 * @param columnView		列宽
	 * @param rowView			行高
	 * @throws RowsExceededException
	 * @throws WriteException
	 */
	public static void addTableHeader(WritableSheet sheet,int startColumn,int row,List<String> headerList,
			WritableCellFormat wcfFormat) throws RowsExceededException, WriteException{
		addTableHeader(sheet, startColumn, row, headerList, wcfFormat, NO_COLUMN_VIEW,NO_COLUMN_VIEW);
	}
	public static void addTableHeader(WritableSheet sheet,int startColumn,int row,List<String> headerList,
			WritableCellFormat wcfFormat,int columnView,int rowView) throws RowsExceededException, WriteException{
		if(headerList!=null && headerList.size()>0){
			Label label=null;
			if(rowView!=NO_COLUMN_VIEW)				//行高
				setRowView(sheet, row, rowView);
			for(int i=0;i<headerList.size();i++){
				if(columnView!=NO_COLUMN_VIEW)		//列宽
					setColumnView(sheet,startColumn+i, columnView);
				label=new Label(startColumn+i,row,headerList.get(i),wcfFormat);
				sheet.addCell(label);
			}
		}
	}
	
	/**
	 * 往表格中添加内容
	 * @param sheet
	 * @param startColumn	开始列
	 * @param row			行号
	 * @param contentList	内容List
	 * @param wcfFormat
	 * @param fieldsStr		对象中需要显示字段的字符串()
	 * @param columnView	列宽
	 * @param rowView       行高
	 * @throws IllegalAccessException 
	 * @throws WriteException 
	 * @throws IllegalArgumentException 
	 * @throws RowsExceededException 
	 */
	public static void addTable(WritableSheet sheet,int startColumn,int row,List contentList,
			WritableCellFormat wcfFormat,String fieldsStr) throws RowsExceededException, IllegalArgumentException, WriteException, IllegalAccessException{
		addTable(sheet, startColumn, row, contentList, wcfFormat, fieldsStr, NO_COLUMN_VIEW,NO_COLUMN_VIEW);
	}
	public static void addTable(WritableSheet sheet,int startColumn,int row,List contentList,
			WritableCellFormat wcfFormat,String fieldsStr,int columnView,int rowView) throws IllegalArgumentException, IllegalAccessException, RowsExceededException, WriteException{
		if(contentList!=null && contentList.size()>0 && fieldsStr!=null){
			String fieldArray[]=fieldsStr.split(FIELD_SEPARATOR);
			Field[] fields=contentList.get(0).getClass().getDeclaredFields();//获得对象的所有属性集合
			Label label=null;
			Field field=null;
			for(int i=0;i<contentList.size();i++){
				if(rowView!=NO_COLUMN_VIEW)				//行高
					setRowView(sheet, row+i, rowView);
				for(int j=0;j<fieldArray.length;j++){
					field=getField(fields, fieldArray[j]);
					field.setAccessible(true);
					if(columnView!=NO_COLUMN_VIEW)		//列宽
						setColumnView(sheet,startColumn+j, columnView);
					label=new Label(startColumn+j,row+i,String.valueOf(field.get(contentList.get(i))),wcfFormat);
					sheet.addCell(label);
				}
			}
		}
	}
	/**
	 * 往表格中添加内容
	 * @param sheet
	 * @param startColumn
	 * @param row
	 * @param contentList		是一个List<List<String>>参数
	 * @param wcfFormat
	 * @param columnView
	 * @param rowView
	 * @throws IllegalArgumentException
	 * @throws IllegalAccessException
	 * @throws RowsExceededException
	 * @throws WriteException
	 */
	public static void addTable(WritableSheet sheet,int startColumn,int row,List<List<String>> contentList,
			WritableCellFormat wcfFormat) throws RowsExceededException, IllegalArgumentException, WriteException, IllegalAccessException{
		addTable(sheet, startColumn, row, contentList, wcfFormat, NO_COLUMN_VIEW,NO_COLUMN_VIEW);
	}
	public static void addTable(WritableSheet sheet,int startColumn,int row,List<List<String>> contentList,
			WritableCellFormat wcfFormat,int columnView,int rowView) throws IllegalArgumentException, IllegalAccessException, RowsExceededException, WriteException{
		if(contentList!=null && contentList.size()>0){
			Label label=null;
			List<String> stringList=null;
			for(int i=0;i<contentList.size();i++){
				if(rowView!=NO_COLUMN_VIEW)				//行高
					setRowView(sheet, row+i, rowView);
				stringList=contentList.get(i);
				if(stringList!=null && stringList.size()>0){
					for(int j=0;j<stringList.size();j++){
						if(columnView!=NO_COLUMN_VIEW)		//列宽
							setColumnView(sheet,startColumn+j, columnView);
						label=new Label(startColumn+j,row+i,String.valueOf(stringList.get(j)),wcfFormat);
						sheet.addCell(label);
					}
				}
			}
		}
	}
	/**
	 * 通过字段名称获取Field对象
	 * @param fields
	 * @param field
	 * @return
	 */
	private static Field getField(Field[] fields,String field){
		Field tempField=null;
		if(fields!=null && fields.length>0){
			for(Field f:fields){
				if(f.getName().equals(field)){
					tempField=f;
					break;
				}
			}
		}
		return tempField;
	}
	
	/**
	 * 获取单元格格式(WritableCellFormat)
	 * @param wFont			WritableFont
	 * @param alignment 	水平对齐方式  ex:Alignment.CENTRE
	 * @param verAlignment  垂直对齐方式
	 * @param isBorder      是否有边框
	 * @param isWrap		是否换行
	 * @param colour		单元格背景颜色
	 * @return
	 * @throws WriteException
	 */
	public static WritableCellFormat getWCFormat(WritableFont wFont) throws WriteException{
		return getWCFormat(wFont, Alignment.CENTRE, VerticalAlignment.CENTRE,true, true, null);
	}
	public static WritableCellFormat getWCFormat(WritableFont wFont,boolean isBorder) throws WriteException{
		return getWCFormat(wFont, Alignment.CENTRE, VerticalAlignment.CENTRE,isBorder, true, null);
	}
	public static WritableCellFormat getWCFormat(WritableFont wFont,Alignment alignment) throws WriteException{
		return getWCFormat(wFont, alignment, VerticalAlignment.CENTRE,true, true, null);
	}
	public static WritableCellFormat getWCFormat(WritableFont wFont,Alignment alignment,VerticalAlignment verAlignment,
			boolean isBorder,boolean isWrap,Colour colour) throws WriteException{
		WritableCellFormat wcfFormat=new WritableCellFormat(wFont);
		wcfFormat.setAlignment(alignment);
		wcfFormat.setVerticalAlignment(verAlignment);
		wcfFormat.setWrap(isWrap);
		if(colour!=null)
			wcfFormat.setBackground(colour);
		if(isBorder)
			wcfFormat.setBorder(Border.ALL,BorderLineStyle.THIN); 
		return wcfFormat;
	}
	
	/**
	 * 获取单元格文字样式(Font)
	 * @param fontName		字体 ex:WritableFont.TAHOMA(宋体)
	 * @param fontSize		字体大小
	 * @param isBold		是否加粗
	 * @param isItalic		是否倾斜
	 * @param isUnderLine	是否加下划线
	 * @param colour		字体颜色
	 * @return
	 * @throws WriteException 
	 */
	public static WritableFont getWFont(int fontSize) throws WriteException{
		return getWFont(WritableFont.TAHOMA, fontSize, false, false, false, Colour.BLACK);
	}
	public static WritableFont getWFont(int fontSize,boolean isBold) throws WriteException{
		return getWFont(WritableFont.TAHOMA, fontSize, isBold, false, false, Colour.BLACK);
	}
	public static WritableFont getWFont(FontName fontName,int fontSize,boolean isBold,
			boolean isItalic,boolean isUnderLine,Colour colour) throws WriteException{
		WritableFont wFont = new WritableFont(fontName,fontSize);
		if(isBold)
			wFont.setBoldStyle(WritableFont.BOLD);
		wFont.setItalic(isItalic);
		if(isUnderLine)
			wFont.setUnderlineStyle(UnderlineStyle.SINGLE);
		wFont.setColour(colour);
		return wFont;
	}
	
	/**
	 * 单元格合并
	 * 合并既可以是横向的，也可以是纵向的。合并后的单元格不能再次进行合并，否则会触发异常
	 * @param sheet
	 * @param startColumn 开始列(从0开始)
	 * @param startRow    开始行
	 * @param endColumn   结束列
	 * @param endRow      结束行  
	 * @throws WriteException 
	 * @throws RowsExceededException 
	 */
	public static void mergeCells(WritableSheet sheet,int startColumn,int startRow,int endColumn,int endRow) throws RowsExceededException, WriteException{
		sheet.mergeCells(startColumn, startRow, endColumn, endRow);
	}
	
	/**
	 * 设置行高
	 * @param sheet
	 * @param row	 行号(从0开始)
	 * @param height 高
	 * @throws RowsExceededException
	 */
	public static void setRowView(WritableSheet sheet,int row,int height) throws RowsExceededException{
		sheet.setRowView(row, height);
	}
	
	/**
	 * 设置列宽
	 * @param sheet
	 * @param column  列号(从0开始)
	 * @param width   宽
	 */
	public static void setColumnView(WritableSheet sheet,int column,int width){
		sheet.setColumnView(column, width);
	}
	
	/**
	 * 添加图片
	 * @param sheet
	 * @param x
	 * @param y
	 * 		#x,y 为起始格
	 * @param width
	 * @param height
	 * 		#width,height 图片占多少个格,并非位置
	 * @param filePath
	 */
	public static void addImage(WritableSheet sheet,int x,int y,int width,int height,String filePath){
		if(!new File(filePath).exists())
			return;
		WritableImage image = new WritableImage(x, y, width, height, new File(filePath));
		sheet.addImage(image);
	}
	
	/**
	 * 关闭WritableWorkbook
	 * @param book
	 */
	public static void closeQuietly(WritableWorkbook book){
		try {
			if (book != null) {
				book.close();
			}
		} catch (Exception ioe) {
			// ignore
		}
	}
	
	/**
	 * 关闭Workbook
	 * @param book
	 */
	public static void closeQuietly(Workbook book){
		try {
			if (book != null) {
				book.close();
			}
		} catch (Exception ioe) {
			// ignore
		}
	}
}
